using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using emp.IDAL;
namespace emp.SqlServerDAL
{
    public class Province :IProvince
    {
        public DataTable selectProvince()
        {
            string SQL_SELECT_PROVICNE = "select * from province";
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_PROVICNE, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }
        public DataTable selectProvince(int user_ID)
        {
            string SQL_SELECT_PROVICNE = "select * from province  where pve_name in (select SUP_PROVINCE from SYSTEM_USER_PROVINCE where SUP_USER_UID = "+ user_ID + ")";
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_PROVICNE, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }
        public DataTable selectCityByProID(int proID)
        {
            string SQL_SELECT_CITY = "select * from city  where city_pve_code = " + proID;
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_CITY, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }
        public DataTable selectCityByProID(string pro)
        {
            string SQL_SELECT_CITY = "select city.* from city ,PROVINCE  where city_pve_code = pve_id and pve_name='"+pro+"'";
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(SQLHelper.CONN_STRING_DTC_INV))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(SQL_SELECT_CITY, conn);
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
            return dt;
        }
    }
}
